Imports System.IO
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports System.Data.Common

Public Class WorkWithData
  Private Sub CreateDataTable()
    ' Create a DataTable filled with information
    ' about files in the current folder.
    ' 
    ' Note the use of the FileInfo and 
    ' DirectoryInfo objects, provided by the 
    ' .NET framework, in the System.IO namespace.

    Dim dt As New DataTable
    dt.Columns.Add("FileName", GetType(System.String))
    dt.Columns.Add("Size", GetType(System.Int64))

    Dim dr As DataRow
    Dim dir As New DirectoryInfo("C:\")
    For Each fi As FileInfo In dir.GetFiles()
      dr = dt.NewRow
      dr(0) = fi.Name
      dr(1) = fi.Length
      dt.Rows.Add(dr)
    Next

    ' Bind the DataGridView to this DataTable.
    demoGrid.DataSource = dt
  End Sub

  Private Sub DataReaderFromSQL()
    Dim strSQL As String = "SELECT * FROM Employees"

    Try
            Using cnn As New OleDbConnection( _
             My.Settings.OleDbConnectionString)
                Using cmd As New OleDbCommand(strSQL, cnn)
                    cnn.Open()

                    Using dr As OleDbDataReader = cmd.ExecuteReader()
                        ' Loop through all the rows, retrieving the 
                        ' columns you need. Also look into the GetString
                        ' method (and other Get... methods) for a faster 
                        ' way to retrieve individual columns.

                        While dr.Read
                            demoList.Items.Add( _
                             String.Format("{0} {1}: {2}", _
                              dr("FirstName"), dr("LastName"), _
                              dr("HomePhone")))
                        End While
                    End Using
                End Using
            End Using

    Catch ex As Exception
      MessageBox.Show(ex.Message)
    End Try
  End Sub

  Private Sub DataSetFromOleDb()
    Dim strSQL As String = _
     "SELECT * FROM Products WHERE CategoryID=1"

    Try
      Using adapter As New OleDbDataAdapter(strSQL, _
        My.Settings.OleDbConnectionString)

        Dim ds As New DataSet
        adapter.Fill(ds, "ProductInfo")

        ' You could look through each row, and 
        ' retrieve the item you want:
        For Each dr As DataRow In _
         ds.Tables("ProductInfo").Rows
          demoList.Items.Add(dr("ProductName").ToString)
        Next dr

        ' Want to bind a grid? It's this easy:
        demoGrid.DataSource = ds.Tables("ProductInfo")
      End Using

    Catch ex As Exception
      MessageBox.Show(ex.Message)
    End Try
  End Sub

  Private Sub DataTableFromSQL()
    Dim strSQL As String = _
      "SELECT * FROM Products WHERE CategoryID = 1"

    Try
      Using adapter As New SqlDataAdapter(strSQL, _
       My.Settings.SqlConnectionString)
        Dim dt As New DataTable
        adapter.Fill(dt)

        For Each dc As DataColumn In _
         dt.Columns

          demoList.Items.Add( _
            String.Format("{0} ({1})", _
            dc.ColumnName, dc.DataType))
        Next dc

        demoGrid.DataSource = dt
      End Using

    Catch ex As Exception
      MessageBox.Show(ex.Message)
    End Try
  End Sub

  Private Sub ReadData(ByVal provider As String)
    Try
      Dim factory As DbProviderFactory = _
       DbProviderFactories.GetFactory(provider)
      Using cnn As DbConnection = factory.CreateConnection()
        cnn.ConnectionString = Common.GetConnectionStringByProvider(provider)

        Using cmd As DbCommand = cnn.CreateCommand()
          cmd.CommandText = "SELECT * FROM Employees"
          cnn.Open()
          Using dr As DbDataReader = cmd.ExecuteReader()
            ' Loop through all the rows, retrieving the 
            ' columns you need.
            demoList.Items.Clear()
            Do While dr.Read
              demoList.Items.Add( _
               String.Format("{0} {1}: {2}", _
                dr("FirstName"), dr("LastName"), _
                dr("HomePhone")))
            Loop
          End Using
        End Using
      End Using
    Catch ex As Exception
            MessageBox.Show(ex.Message)
    End Try
  End Sub

  Private Overloads Sub PrepareDemo(ByVal ShowGrid As Boolean)
    PrepareDemo(ShowGrid, pgeListBox)
  End Sub

  Private Overloads Sub PrepareDemo(ByVal ShowGrid As Boolean, ByVal SelectedPage As TabPage)

    demoList.Items.Clear()
    demoGrid.Visible = ShowGrid
    tabDemo.SelectedTab = SelectedPage
  End Sub

  Private Sub sqlDataReaderButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles sqlDataReaderButton.Click
    PrepareDemo(False)
    BreakIfChecked(Me.chkDebug)

    DataReaderFromSQL()
  End Sub

  Private Sub sqlDataSetButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles sqlDataTableButton.Click
    PrepareDemo(True)
    BreakIfChecked(Me.chkDebug)

    DataTableFromSQL()
  End Sub

  Private Sub dataTableButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dataTableButton.Click
    PrepareDemo(True, pgeGrid)
    BreakIfChecked(Me.chkDebug)

    CreateDataTable()
  End Sub

  Private Sub oleDbDataSetButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles oleDbDataSetButton.Click
    PrepareDemo(True)
    BreakIfChecked(Me.chkDebug)

    DataSetFromOleDb()
  End Sub

  Private Sub oleDbButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles oleDbButton.Click
    PrepareDemo(False)
    BreakIfChecked(Me.chkDebug)

    ReadData("System.Data.OleDb")
  End Sub

  Private Sub sqlClientButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles sqlClientButton.Click
    PrepareDemo(False)
    BreakIfChecked(Me.chkDebug)

    ReadData("System.Data.SqlClient")
  End Sub

End Class